﻿Use Agile8
GO

--查看计算结果
Declare @Cols nvarchar(4000),
        @SalaryMonth datetime,
        @Sql nvarchar(4000)
Set @SalaryMonth='20201201'
Select @Cols=Isnull(@Cols+',','')+Rtrim(Quotename(Name))
FROM dbo.reward_salarycolumn WHERE ParentID='66b1c439-c32b-e51d-75c5-39f98d3b49f8'

Set @Sql=N'
;With CTE_Salary As
(
   SELECT a.UserID,b.Name AS UserName,o.ID AS OrgID,o.name AS OrgName,a.SalaryMonth,c.Name AS SalaryItem,a.Amount FROM dbo.reward_salary a
	INNER JOIN dbo.rbac_user AS b ON b.ID=a.UserID
	INNER JOIN dbo.base_relevance AS r ON r.FirstId=a.UserID
	INNER JOIN dbo.rbac_organize AS o ON o.ID=r.SecondId
	INNER JOIN dbo.reward_salarycolumn AS c ON c.ID=a.SalaryColumnID
	Where SalaryMonth=@SalaryMonth
)
Select * From CTE_Salary Pivot(Max(Amount) For SalaryItem In('+@Cols+')) As b
'
Exec sp_executesql  @Sql,N'@SalaryMonth datetime',@SalaryMonth




SET IDENTITY_INSERT [ScheduleTask] ON 

INSERT [ScheduleTask] ([Id], [Name], [Seconds], [Type], [Enabled], [StopOnError], [LastStartUtc], [LastEndUtc], [LastSuccessUtc]) VALUES (1, N'Send emails', 60, N'Nop.Services.Messages.QueuedMessagesSendTask, Nop.Services', 1, 0, NULL, NULL, NULL)
INSERT [ScheduleTask] ([Id], [Name], [Seconds], [Type], [Enabled], [StopOnError], [LastStartUtc], [LastEndUtc], [LastSuccessUtc]) VALUES (2, N'Keep alive', 300, N'Nop.Services.Common.KeepAliveTask, Nop.Services', 1, 0, NULL, NULL, NULL)
INSERT [ScheduleTask] ([Id], [Name], [Seconds], [Type], [Enabled], [StopOnError], [LastStartUtc], [LastEndUtc], [LastSuccessUtc]) VALUES (3, N'Delete guests', 600, N'Nop.Services.Customers.DeleteGuestsTask, Nop.Services', 1, 0, NULL, NULL, NULL)
INSERT [ScheduleTask] ([Id], [Name], [Seconds], [Type], [Enabled], [StopOnError], [LastStartUtc], [LastEndUtc], [LastSuccessUtc]) VALUES (4, N'Clear cache', 600, N'Nop.Services.Caching.ClearCacheTask, Nop.Services', 0, 0, NULL, NULL, NULL)
INSERT [ScheduleTask] ([Id], [Name], [Seconds], [Type], [Enabled], [StopOnError], [LastStartUtc], [LastEndUtc], [LastSuccessUtc]) VALUES (5, N'Clear log', 3600, N'Nop.Services.Logging.ClearLogTask, Nop.Services', 0, 0, NULL, NULL, NULL)
INSERT [ScheduleTask] ([Id], [Name], [Seconds], [Type], [Enabled], [StopOnError], [LastStartUtc], [LastEndUtc], [LastSuccessUtc]) VALUES (6, N'Update currency exchange rates', 3600, N'Nop.Services.Directory.UpdateExchangeRateTask, Nop.Services', 1, 0, NULL, NULL, NULL)
SET IDENTITY_INSERT [ScheduleTask]  OFF


